package com.deliccio.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.deliccio.connect.DAO;
import com.deliccio.dao.GridDAO;
import com.deliccio.entitys.Cliente;
import com.deliccio.entitys.Columna;
import com.deliccio.entitys.Grid;
import com.deliccio.entitys.Usuario;
import com.deliccio.util.BeanConsulta;

@Repository("GridDAO")
public class GridDAOImpl extends DAO<Grid> implements GridDAO{


	private String createQuery(boolean iscount, Grid grid,
			boolean _search,
			String searchField,String searchOper,String searcchString,
			String sidx,String sord,BeanConsulta[] objConsultas){
		
		String sql=" Select ";
		
		if(iscount){
			sql+=" count(p.id) ";
		}else{
			List<Columna> lst= new ArrayList<Columna>();
			for(int i=0;i <grid.getColumnaList().size();i++){
				Columna c=grid.getColumnaList().get(i);
				if(c.getMapping()){
					lst.add(c);
				}						
			}
			
			for(int i=0;i <lst.size();i++){
				Columna c=lst.get(i);			
				sql+=" p."+ c.getTitulo()+ " ";	
				if(c.getMapping()){
					if(i<lst.size()-1){
						sql+=" , ";
					}
				}
			}
		}
		sql+=" from "+grid.getTablaName()+ " p ";
		
		sql+=" where p.id>0 ";
		
		if(objConsultas!=null&&objConsultas.length>0){			
			sql+=" and ";
			for(int i=0;i < objConsultas.length;i++){
				BeanConsulta ojb=objConsultas[i];
				
				if(ojb.getIgual()){
					Double d=Double.parseDouble(String.valueOf(ojb.getValor()));
					if(d>0){
						sql+=" p."+ojb.getAtributo()+" = :" +  ojb.getQuery();
						if(i<objConsultas.length-1){
							sql+=" and ";
						}	
					}
				
				}
				if(ojb.getLike()){
					String str=String.valueOf(ojb.getValor());
					if(!str.equals("")){
						sql+=" p."+ojb.getAtributo()+" like :" +  ojb.getQuery();
						if(i<objConsultas.length-1){
							sql+=" and ";
						}	
					}					
				}
				if(ojb.getMenos()){
					Double d=Double.parseDouble(String.valueOf(ojb.getValor()));
					if(d>0){
						sql+=" p."+ojb.getAtributo()+" > :" +  ojb.getQuery();
						if(i<objConsultas.length-1){
							sql+=" and ";
						}	
					}
				
				}
				if(ojb.getMayor()){
					Double d=Double.parseDouble(String.valueOf(ojb.getValor()));
					if(d>0){
						sql+=" p."+ojb.getAtributo()+" < :" +  ojb.getQuery();
						if(i<objConsultas.length-1){
							sql+=" and ";
						}	
					}					
				}
				
				if(ojb.getBetween()){
					Double d=Double.parseDouble(String.valueOf(ojb.getValor()));
					if(d>0){
						sql+=" p."+ojb.getAtributo()+"  between :" +  ojb.getQuery() +" and :"+ ojb.getQuery2();
						if(i<objConsultas.length-1){
							sql+=" and ";
						}	
					}
				}				
				
				
			}
		}
		System.out.println(sql);
		
		return sql;
	}

	@Override
	public int getCantidadDataRows(Grid grid,Usuario usuario,int codGrid,String sidx,String sord,int page,
			int rows,boolean _search,String searchField,String searchOper,String searchString){
		sqlQuery=createQuery(true,grid,_search,searchField,searchOper,searchString,sidx,sord,null);
		q=em.createQuery(sqlQuery);
		Long c=(Long) q.getSingleResult();
		return c.intValue();
	}

	@Override
	public List<Map<String,Object>> getData(
			Grid grid,Usuario usuario,int codGrid,String sidx,String sord,
			int page,int rows,boolean _search,String searchField,
			String searchOper,String searchString){
		
		sqlQuery=createQuery(false,grid,_search,searchField,searchOper,searchString,sidx,sord,null);
		q=em.createQuery(sqlQuery);	
		q.setFirstResult(rows*page-rows);
		q.setMaxResults(rows);
		List<Object[]> retorno=q.getResultList();	
		System.out.println(retorno);
		if(retorno.size() > 0){
			List<Map<String,Object>> salida=new ArrayList<Map<String,Object>>();
			for(Object[] dato : retorno){
				Map<String,Object> data=new HashMap<String,Object>();
				for(int i=0;i < dato.length;i++){
					data.put(grid.getColumnaList().get(i).getCodigo(),dato[i]);
				}
				salida.add(data);
			}
			return salida;
		}
		return null;
	}



	@Override
	public int getCantidadDataRows(Grid grid,
			Cliente usuario,int codGrid,String sidx,String sord,int page,int rows,
			boolean _search,String searchField,String searchOper,String searchString,BeanConsulta[] objConsultas){
		sqlQuery=createQuery(true,grid,_search,searchField,searchOper,searchString,sidx,sord,objConsultas);
		q=em.createQuery(sqlQuery);		
		if(objConsultas!=null&&objConsultas.length>0){			
			for(int i=0;i < objConsultas.length;i++){
				BeanConsulta ojb=objConsultas[i];
				
				if(ojb.getIgual()){	
					Double d=Double.parseDouble(String.valueOf(ojb.getValor()));
					if(d>0){
						q.setParameter(ojb.getQuery(),ojb.getValor());						
					}
				}
				
				if(ojb.getLike()){
					String str=String.valueOf(ojb.getValor());
					if(!str.equals("")){
						q.setParameter(ojb.getQuery(),"%"+ojb.getValor()+"%");						
					}
				}
				
				if(ojb.getMenos()){
					Double d=Double.parseDouble(String.valueOf(ojb.getValor()));
					if(d>0){
						q.setParameter(ojb.getQuery(),ojb.getValor());
					}					
				}
				if(ojb.getMayor()){
					Double d=Double.parseDouble(String.valueOf(ojb.getValor()));
					if(d>0){
						q.setParameter(ojb.getQuery(),ojb.getValor());
					}					
				}				
				
				if(ojb.getBetween()){
					Double d=Double.parseDouble(String.valueOf(ojb.getValor()));
					if(d>0){
						q.setParameter(ojb.getQuery(),ojb.getValor());
						q.setParameter(ojb.getQuery2(),ojb.getValor2());
					}					
				}
						
					
			}
		}
		Long c=(Long) q.getSingleResult();
		return c.intValue();
	}

	@Override
	public List<Map<String,Object>> getData(Grid grid,Cliente usuario,int codGrid,String sidx,String sord,int page,int rows,
			boolean _search,String searchField,String searchOper,String searchString,BeanConsulta[] objConsultas){

		sqlQuery=createQuery(false,grid,_search,searchField,searchOper,searchString,sidx,sord,objConsultas);
		q=em.createQuery(sqlQuery);	
		q.setFirstResult(rows*page-rows);
		q.setMaxResults(rows);
		
		if(objConsultas!=null&&objConsultas.length>0){			
			for(int i=0;i < objConsultas.length;i++){
				BeanConsulta ojb=objConsultas[i];
				
				if(ojb.getIgual()){	
					Double d=Double.parseDouble(String.valueOf(ojb.getValor()));
					if(d>0){
						q.setParameter(ojb.getQuery(),ojb.getValor());						
					}
				}
				
				if(ojb.getLike()){
					String str=String.valueOf(ojb.getValor());
					if(!str.equals("")){
						q.setParameter(ojb.getQuery(),"%"+ojb.getValor()+"%");						
					}
				}
				
				if(ojb.getMenos()){
					Double d=Double.parseDouble(String.valueOf(ojb.getValor()));
					if(d>0){
						q.setParameter(ojb.getQuery(),ojb.getValor());
					}					
				}
				if(ojb.getMayor()){
					Double d=Double.parseDouble(String.valueOf(ojb.getValor()));
					if(d>0){
						q.setParameter(ojb.getQuery(),ojb.getValor());
					}					
				}				
				
				if(ojb.getBetween()){
					Double d=Double.parseDouble(String.valueOf(ojb.getValor()));
					if(d>0){
						q.setParameter(ojb.getQuery(),ojb.getValor());
						q.setParameter(ojb.getQuery2(),ojb.getValor2());
					}					
				}
			}
		}
		
		List<Object[]> retorno=q.getResultList();	
		System.out.println(retorno);
		if(retorno.size() > 0){
			List<Map<String,Object>> salida=new ArrayList<Map<String,Object>>();
			for(Object[] dato : retorno){
				Map<String,Object> data=new HashMap<String,Object>();
				for(int i=0;i < dato.length;i++){
					data.put(grid.getColumnaList().get(i).getCodigo(),dato[i]);
				}
				for(int i=0;i < grid.getColumnaList().size();i++){
					Columna c=grid.getColumnaList().get(i);
					if(!c.getMapping()){
						data.put(c.getCodigo(),"");
					}
				}
				salida.add(data);
			}
			return salida;
		}
		return null;
	}

	@Override
	public Grid obtenerPorCodigoTabla(Integer cod){			
		sqlQuery=" select new Grid(g.id,g.codigo,g.nombre,g.codtabla,g.tablaName) from Grid g  where  g.codtabla = :codtabla ";
		q=em.createQuery(sqlQuery);
		q.setMaxResults(1);
		q.setParameter("codtabla",cod);		
		return (Grid) q.getSingleResult();
	}

	@Override
	public List<Map<String, Object>> getData(Grid grid, Usuario usuario,
			int codGrid, String sidx, String sord, int page, int rows,
			boolean _search, String searchField, String searchOper,
			String searchString, BeanConsulta[] objConsultas) {
		sqlQuery=createQuery(false,grid,_search,searchField,searchOper,searchString,sidx,sord,objConsultas);
		q=em.createQuery(sqlQuery);	
		q.setFirstResult(rows*page-rows);
		q.setMaxResults(rows);
		
		if(objConsultas!=null&&objConsultas.length>0){			
			for(int i=0;i < objConsultas.length;i++){
				BeanConsulta ojb=objConsultas[i];
				
				if(ojb.getIgual()){	
					Double d=Double.parseDouble(String.valueOf(ojb.getValor()));
					if(d>0){
						q.setParameter(ojb.getQuery(),ojb.getValor());						
					}
				}
				
				if(ojb.getLike()){
					String str=String.valueOf(ojb.getValor());
					if(!str.equals("")){
						q.setParameter(ojb.getQuery(),"%"+ojb.getValor()+"%");						
					}
				}
				
				if(ojb.getMenos()){
					Double d=Double.parseDouble(String.valueOf(ojb.getValor()));
					if(d>0){
						q.setParameter(ojb.getQuery(),ojb.getValor());
					}					
				}
				if(ojb.getMayor()){
					Double d=Double.parseDouble(String.valueOf(ojb.getValor()));
					if(d>0){
						q.setParameter(ojb.getQuery(),ojb.getValor());
					}					
				}				
				
				if(ojb.getBetween()){
					Double d=Double.parseDouble(String.valueOf(ojb.getValor()));
					if(d>0){
						q.setParameter(ojb.getQuery(),ojb.getValor());
						q.setParameter(ojb.getQuery2(),ojb.getValor2());
					}					
				}
			}
		}
		
		List<Object[]> retorno=q.getResultList();	
		System.out.println(retorno);
		if(retorno.size() > 0){
			List<Map<String,Object>> salida=new ArrayList<Map<String,Object>>();
			for(Object[] dato : retorno){
				Map<String,Object> data=new HashMap<String,Object>();
				for(int i=0;i < dato.length;i++){
					data.put(grid.getColumnaList().get(i).getCodigo(),dato[i]);
				}
				for(int i=0;i < grid.getColumnaList().size();i++){
					Columna c=grid.getColumnaList().get(i);
					if(!c.getMapping()){
						data.put(c.getCodigo(),"");
					}
				}
				salida.add(data);
			}
			return salida;
		}
		return null;
	}

}
